Microsoft Excel Statistical and Advanced Functions for Decision Making by Palani Murugappan

Author:Palani Murugappan [Murugappan, Palani]
Language: eng
Format: epub
Publisher: Blue Micro Solutions
Published: 2014-09-10T22:00:00+00:00

Note that in Excel you can perform the correlation coefficient (r value) calculation using the CORREL function. To perform this calculation for the above, try the following steps.

In cell C16, type the following formula i.e. =CORREL(B2:B13,C2:C13)

You should achieve the same result as in cell B16 i.e. the r value of 0.93 (after rounding off).

The above formula is as follows: =CORREL(range1, range2) where range1 and range 2 are data sets with the same number of elements. As mentioned before, a value of 1 reflects a perfect positive linear relationship; a value of 0 indicates no relationship; and a value of -1 reflects a perfect negative linear relationship.

Let us look at yet another example. One should bear in mind that just because two variables are strongly correlated does not imply that there is a relationship between them. The following example highlights this.

Assume a city is going through some bad weather. Due to this, the number of pneumonia patients admitted in a hospital on a weekly basis over a three month period is inversely correlated with the mean temperature as the colder weather contributed to people falling sick.

Also, due to the wet weather, the sales of umbrellas have increased. Now you can correlate the number of pneumonia cases with the sales of umbrellas as both were influenced by the weather independently.

Furthermore, you can correlate the pneumonia cases with the sales of pesticides as completely unrelated factors have caused both the variables to increase in the three month period.

Coming back to the CORREL function, care should be exercised when the CORREL function indicates a linear relationship. When this takes place, all you can say is that you have a reason to believe that a linear relationship exists. You still need to explore the relationship further as the r value does not say anything about the relationship. In this case, it might be useful to determine the slope and intercept of the line that best fits the data set collected.

“Best-fit” curve

The line of “best fit” to a set of data can be used to analyze many observations visually and performing some mathematical calculations. However, we are more concerned as to how Excel can help perform these calculations using some of its built-in functions.

Most of the time, a XY (Scatter) plot is used to create a chart and observe if a relationship exists based on the curve and r value calculated. Instead of calculating some of these values manually, Excel can be used to calculate the goodness-of-fit (R2) value for any given curve. This approach is a better method when compared to using the CORREL function.

To recap, the correlation coefficient (r) is calculated from the square root of the R2 value. R2 is preferable to be used as its value is the fraction of the variance of the data explained by the fitted model.

Next comes the question – how well does the model fit the data?

To answer the above, a technique known as regression is used. For a straight line, the line of “best fit” is

Download

Copyright Disclaimer:
This site does not store any files on its server. We only index and link to content provided by other sites. Please contact the content providers to delete copyright contents if any and email us, we'll remove relevant links or contents immediately.